#include "report.h"
#include <QtXlsx/QtXlsx>
#include <QModelIndex>
#include <QSettings>
#include "smtp.h"
#include <QProcess>
#include "QtXlsx/xlsxcellrange.h"

Report::Report(QObject *parent, QSqlDatabase *db) :
    QThread(parent),stopped(true)
{
    this->db = *db;
    model = new QSqlQueryModel;
    sql = tr("SELECT"
             " tb_dev_corrected_value_conf.cinema_hall,"
             " tb_dev_corrected_value_conf.dev_ip"
             " FROM"
             " tb_dev_corrected_value_conf");
    model->setQuery(sql, this->db);
    if(model->lastError().isValid())
    {
        emit log_info("report error:" + model->lastError().text());
        exit(0);
    }

    for(int i = 0; i<model->rowCount(); i++)
    {
        theaterNoList<<model->data(model->index(i, 0)).toString();
    }

    QSettings *settings = new QSettings("config.ini", QSettings::IniFormat);
    test_time = settings->value("EMAIL/report_time").toTime();
    user = settings->value("EMAIL/user_name").toString();
    passwd = settings->value("EMAIL/pass_word").toString();
    host = settings->value("EMAIL/email_server").toString();
    port = settings->value("EMAIL/email_server_port").toInt();
    to = settings->value("EMAIL/recpt").toString();
    from = settings->value("EMAIL/user_name").toString();
 }

void Report::mailSent(QString msg)
{
    qDebug()<<"msg="<<msg<<endl;
    emit log_info(msg);
}

void Report::run()
{
    doExcel();
    emit log_info(tr("发送邮件"));
}

void Report::doExcel()
{
#if 1
    QXlsx::Format format;
    format.setFontSize(12);
    format.setFontBold(true);
    format.setFontColor(QColor(255, 0, 0));
    format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    QXlsx::Document xlsx;
    for(int i = 0; i<theaterNoList.size(); i++)
    {
        sql = tr("SELECT"
                 " tb_dev_test_results.lc,"
                 " tb_dev_test_results.rc,"
                 " tb_dev_test_results.cc,"
                 " tb_dev_test_results.hb,"
                 " tb_dev_test_results.la,"
                 " tb_dev_test_results.ra,"
                 " tb_dev_test_results.cct,"
                 " tb_dev_test_results.rx,"
                 " tb_dev_test_results.ry,"
                 " tb_dev_test_results.gx,"
                 " tb_dev_test_results.gy,"
                 " tb_dev_test_results.bluex,"
                 " tb_dev_test_results.bluey,"
                 " tb_dev_test_results.white_ftl,"
                 " tb_dev_test_results.red_ftl,"
                 " tb_dev_test_results.green_ftl,"
                 " tb_dev_test_results.blue_ftl,"
                 " tb_dev_test_results.temper_c,"
                 " tb_dev_test_results.stime"
                 " FROM"
                 " tb_dev_test_results where cinema_hall = '%1' order by id desc limit 7")
                .arg(theaterNoList.at(i));
        model->setQuery(sql, db);
        if(model->lastError().isValid())
        {
            emit log_info("report error:" + model->lastError().text());
        }
        xlsx.addSheet(theaterNoList.at(i));
        xlsx.write("A1", tr("左声道(dB)"), format);
        xlsx.write("B1", tr("右声道(dB)"), format);
        xlsx.write("C1", tr("中置(dB)") ,format);
        xlsx.write("D1", tr("重低音(dB)"), format);
        xlsx.write("E1", tr("左环(dB)"), format);
        xlsx.write("F1", tr("右环(dB)"), format);
        xlsx.write("G1", tr("色温"), format);
        xlsx.write("H1", tr("红光色域(x,y)"), format);
        xlsx.write("I1", tr("绿光色域(x,y)"), format);
        xlsx.write("J1", tr("蓝光色域(x,y)"), format);
        xlsx.write("K1", tr("白光亮度(fTL)"), format);
        xlsx.write("L1", tr("红光亮度(fTL)"), format);
        xlsx.write("M1", tr("绿光亮度(fTL)"), format);
        xlsx.write("N1", tr("蓝光亮度(fTL)"), format);
        xlsx.write("O1", tr("摄氏度(℃)"), format);
        xlsx.write("P1", tr("测试时间"), format);
        format.setFontColor(QColor(0, 0, 0));
        int count = model->rowCount();
        for(int row = 0; row < count; row++ )
        {
            format.setFontBold(false);
            // 声压
            xlsx.write(row+2, 1, model->data(model->index(row, 0)).toString(), format);
            xlsx.write(row+2, 2, model->data(model->index(row, 1)).toString(), format);
            xlsx.write(row+2, 3, model->data(model->index(row, 2)).toString(), format);
            xlsx.write(row+2, 4, model->data(model->index(row, 3)).toString(), format);
            xlsx.write(row+2, 5, model->data(model->index(row, 4)).toString(), format);
            xlsx.write(row+2, 6, model->data(model->index(row, 5)).toString(), format);
            //色温
            xlsx.write(row+2, 7, model->data(model->index(row, 6)).toString(), format);
            // 色域
            QString rx, ry, gx, gy, bx, by;
            rx = model->data(model->index(row, 7)).toString();
            ry = model->data(model->index(row, 8)).toString();
            gx = model->data(model->index(row, 9)).toString();
            gy = model->data(model->index(row, 10)).toString();
            bx = model->data(model->index(row, 11)).toString();
            by = model->data(model->index(row, 12)).toString();
            QString red = tr("(%1, %2)").arg(rx).arg(ry);
            QString green = tr("(%1, %2)").arg(gx).arg(gy);
            QString blue = tr("(%1, %2)").arg(bx).arg(by);
            xlsx.write(row+2, 8, red, format);
            xlsx.write(row+2, 9, green, format);
            xlsx.write(row+2, 10, blue, format);
            //亮度
            xlsx.write(row+2, 11, model->data(model->index(row, 13)).toString(), format);
            xlsx.write(row+2, 12, model->data(model->index(row, 14)).toString(), format);
            xlsx.write(row+2, 13, model->data(model->index(row, 15)).toString(), format);
            xlsx.write(row+2, 14, model->data(model->index(row, 16)).toString(), format);
            //温度
            xlsx.write(row+2, 15, model->data(model->index(row, 17)).toString(), format);
            //时间
            xlsx.write(row+2, 16, model->data(model->index(row, 18)).toString(), format);
        }
    }

#endif

    QString current_datetime = QDateTime::currentDateTime().toString("yyyy-MM-dd");
    QString file = "report_" + current_datetime + ".xlsx";
    QString filePath = "D:\\report\\" + file;
    bool flag = xlsx.saveAs(filePath);
    emit send_message(filePath);
}

void Report::doExcelEx()
{
    QXlsx::Format format;
    format.setFontSize(12);
    format.setFontBold(true);
    format.setFontColor(QColor(255, 0, 0));
    format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    QXlsx::Document xlsx;
    xlsx.addSheet(tr("映厅质量测试报告"));
    for(int i = 0; i<theaterNoList.size(); i++)
    {
        sql = tr("SELECT"
                 " tb_dev_test_results.lc,"
                 " tb_dev_test_results.rc,"
                 " tb_dev_test_results.cc,"
                 " tb_dev_test_results.hb,"
                 " tb_dev_test_results.la,"
                 " tb_dev_test_results.ra,"
                 " tb_dev_test_results.white_ftl,"
                 " tb_dev_test_results.stime"
                 " FROM"
                 " tb_dev_test_results where cinema_hall = '%1' order by id desc limit 7")
                .arg(theaterNoList.at(i));
        model->setQuery(sql, db);
        if(model->lastError().isValid())
        {
            emit log_info("report error:" + model->lastError().text());
        }


        int count = model->rowCount();
        for(int row = 0; row < count; row++ )
        {
            format.setFontBold(true);
            format.setFontSize(26);
            xlsx.write(row+1,1, theaterNoList.at(i), format);
            xlsx.mergeCells("A"+QString::number(row+1) + ":H" +QString::number(row+1), format);
            format.setFontSize(12);
            xlsx.write(row+2,2, tr("左声道(dB)"), format);
            xlsx.write(row+2,3, tr("右声道(dB)"), format);
            xlsx.write(row+2,4, tr("中置(dB)") ,format);
            xlsx.write(row+2,5, tr("重低音(dB)"), format);
            xlsx.write(row+2,6, tr("左环(dB)"), format);
            xlsx.write(row+2,7, tr("右环(dB)"), format);
            xlsx.write(row+2,8, tr("白光亮度(fTL)"), format);
            xlsx.write(row+2,9, tr("摄氏度(℃)"), format);
            xlsx.write(row+2,10, tr("测试时间"), format);
            format.setFontBold(false);
            format.setFontColor(QColor(0, 0, 0));
            // 声压
            xlsx.write(row+3, 1, model->data(model->index(row, 0)).toString(), format);
            xlsx.write(row+3, 2, model->data(model->index(row, 1)).toString(), format);
            xlsx.write(row+3, 3, model->data(model->index(row, 2)).toString(), format);
            xlsx.write(row+3, 4, model->data(model->index(row, 3)).toString(), format);
            xlsx.write(row+3, 5, model->data(model->index(row, 4)).toString(), format);
            xlsx.write(row+3, 6, model->data(model->index(row, 5)).toString(), format);
            //亮度
            xlsx.write(row+3, 7, model->data(model->index(row, 6)).toString(), format);
            //时间
            xlsx.write(row+3, 8, model->data(model->index(row, 7)).toString(), format);
        }
    }

    QString current_datetime = QDateTime::currentDateTime().toString("yyyy-MM-dd");
    QString file = "report_" + current_datetime + ".xlsx";
    QString filePath = "D:\\report\\" + file;
    bool flag = xlsx.saveAs(filePath);
    emit send_message(filePath);
}
